﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.DBCommand.Examination
{
    public class GetAnswerByQuestionListDbCommand : ICommand<IList<AnswerDTO>>
    {
        private readonly IList<int> _questionIds;
        private const string _Sql = @" 
                                        SELECT * FROM Answer
                                         WHERE QuestionId IN ({0})
                                 
                                ";
        public GetAnswerByQuestionListDbCommand(IList<int> questionIds)
        {
            _questionIds = questionIds;
        }

        public IList<AnswerDTO> Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(BuildQuery(), con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.Text;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return GetDtos(dt);
            }
        }

        private string BuildQuery()
        {
            string ids = string.Join(",", _questionIds.ToArray());
            return string.Format(_Sql, ids);
        }
        private IList<AnswerDTO> GetDtos(DataTable dtTable)
        {
            var answerDTOs = new List<AnswerDTO>();
            foreach (DataRow dtRow in dtTable.Rows)
            {
                var answerDTO = new AnswerDTO();
                answerDTO.QuestionId = (int)dtRow["QuestionId"];
                answerDTO.AnswerId = (int)dtRow["AnswerId"];
                answerDTO.AnswerContent = dtRow["Content"].ToString();
                answerDTOs.Add(answerDTO);
            }
            return answerDTOs;
        }
    }
}
